/******      Altere o Nome do Database abaixo
*/
USE [CASASHOW]
GO

/******      Consistindo a existencia da Procedure [dbo].[SP01_CRUD_USUARIO]  
*/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP01_CRUD_USUARIO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP01_CRUD_USUARIO]
GO

/****** Object:     StoredProcedure [dbo].[SP01_CRUD_USUARIO] 
       Repetir o bloco abaixo para cada manutencao
       
       Release:     0
       Author:             <Nome do Programador>
       Date:        <Data da criacao/alteracao>
       Remarks:     <Descricao do objetivo da procedure e/ou  da razao da manutencao>
*/  
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP01_CRUD_USUARIO]
       @XML XML
AS
/******      Declaracao das variaveis locais - NUNCA utilize os parametros de entrada nos comandos SQL 
*/

DECLARE @V_XML XML
SET @V_XML = @XML

/******      Crie a logica da procedure abaixo
*/

DECLARE @ERRO AS INT
SET @ERRO = 0

SELECT         
	doc.col.value('ID_USUARIO[1]', 'INT') AS ID_USUARIO,    
	doc.col.value('NM_USUARIO[1]','VARCHAR(45)') AS NM_USUARIO,    
	doc.col.value('CD_CPF[1]','VARCHAR(20)') AS CD_CPF, 
	doc.col.value('CD_TELEFONE[1]','VARCHAR(200)') AS CD_TELEFONE ,
	doc.col.value('DT_CADASTRO[1]', 'DATETIME') AS DT_CADASTRO,    
	doc.col.value('NM_EMAIL[1]','VARCHAR(100)') AS NM_EMAIL,    
	doc.col.value('NM_SENHA[1]','VARCHAR(45)') AS NM_SENHA, 
	doc.col.value('NM_LOGIN[1]','VARCHAR(45)') AS NM_LOGIN ,
	doc.col.value('ID_PERFIL[1]', 'INT') AS ID_PERFIL,    
	doc.col.value('IC_USUARIO_DISPONIVEL[1]','BIT') AS IC_USUARIO_DISPONIVEL,    
	doc.col.value('ACAO[1]','VARCHAR(45)') AS ACAO INTO #TEMP_USUARIO   
FROM @V_XML.nodes('ArrayOfUsuario/Usuario') doc(col)

IF EXISTS(SELECT TOP 1 1 FROM #TEMP_USUARIO WITH(NOLOCK) WHERE UPPER(ACAO) = 'INCLUIR')    
BEGIN
INSERT INTO TB02_USUARIO 
		(NM_USUARIO,
		CD_CPF,
		CD_TELEFONE,
		DT_CADASTRO,
		NM_EMAIL,
		NM_SENHA,
		NM_LOGIN,
		ID_PERFIL,
		IC_USUARIO_DISPONIVEL)
			SELECT TEMP.NM_USUARIO,
				TEMP.CD_CPF,
				TEMP.CD_TELEFONE,
				CONVERT(SMALLDATETIME,TEMP.DT_CADASTRO,103) AS DT_CADASTRO ,
				TEMP.NM_EMAIL,
				TEMP.NM_SENHA,
				TEMP.NM_LOGIN,
				TEMP.ID_PERFIL,
				TEMP.IC_USUARIO_DISPONIVEL
		FROM #TEMP_USUARIO TEMP WITH(NOLOCK) WHERE UPPER(TEMP.ACAO) = 'INCLUIR'
END

IF EXISTS(SELECT TOP 1 1 FROM #TEMP_USUARIO WITH(NOLOCK) WHERE UPPER(ACAO) = 'EXCLUIR')    
BEGIN    
DELETE FROM TB02_USUARIO    
WHERE ID_USUARIO IN (SELECT ID_USUARIO FROM #TEMP_USUARIO WITH(NOLOCK) WHERE UPPER(ACAO) = 'EXCLUIR')
END

IF EXISTS(SELECT TOP 1 1 FROM #TEMP_USUARIO WITH(NOLOCK) WHERE UPPER(ACAO) = 'ALTERAR')    
BEGIN    
UPDATE USUA
SET USUA.NM_USUARIO = TEMP.NM_USUARIO,
USUA.CD_CPF = TEMP.CD_CPF,
USUA.CD_TELEFONE = TEMP.CD_TELEFONE,
USUA.DT_CADASTRO = CONVERT(SMALLDATETIME,TEMP.DT_CADASTRO,103),
USUA.NM_EMAIL = TEMP.NM_EMAIL,
USUA.NM_SENHA = TEMP.NM_SENHA,
USUA.NM_LOGIN = TEMP.NM_LOGIN,
USUA.ID_PERFIL = TEMP.ID_PERFIL,
USUA.IC_USUARIO_DISPONIVEL = TEMP.IC_USUARIO_DISPONIVEL   
FROM #TEMP_USUARIO TEMP
INNER JOIN TB02_USUARIO USUA WITH(NOLOCK) ON USUA.ID_USUARIO = TEMP.ID_USUARIO
WHERE UPPER(TEMP.ACAO) = 'ALTERAR'

END

DROP TABLE #TEMP_USUARIO  
GO


/******      Atribua o Grant EXECUTE adequado a procedure [dbo].[SP01_CRUD_USUARIO] 
*/
GRANT EXECUTE ON [dbo].[SP01_CRUD_USUARIO] TO [root]
GO

